"""
author : wlingforever
file name : db_download
last modified time:2022.10.7

description:
    download(select) data from database

"""
import pymysql

# from pymysql import MySQLError
# 建立连接
# host 主机 port 端口
# charset=字符集

username = 'rdmanager'
ur_password = 'rdm2022.'
# username = 'rdquery'
# ur_password = 'rdquery2022'

pnw = 'localhost'
#
# pnw = '100.126.31.240' # lib iv4 (yes)

# pnw = '110.53.160.18' # lib pub (can not connect)
#
# pnw = '100.65.255.40' # rom iv4
# pnw = '175.13.175.247' # rom pub

# pnw = '100.67.113.157' # LIB CSU iv4
# pnw = '175.11.249.143' # LIB CSU pub (yes)will be changed ever change the connection time


coon = pymysql.connect(host=pnw, port=3306,
                       user=username, password=ur_password,
                       database='rfo_detection', charset='utf8mb4')

try:
    # print(coon)
    # 获取游标对象
    with coon.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
        # 通过对象向数据库发sql语句，获取执行结果
        sql_select = "select key_id,image_name, image_path,detect_time from tb_i_data "
        cursor.execute(sql_select)
        # result is a dict
        # print(cursor.fetchone())
        image = []
        for row in iter(lambda: cursor.fetchone(), None):
            image.append(row)
        # row = cursor.fetchone()
        # while row:
        #     image.append(row)
        #     row = cursor.fetchone()
        print("images find成功!")

        print(image)
        print(len(image))

        # get targets
        # 通过对象向数据库发sql语句，获取执行结果
        sql_select = "select key_id,target_pn,class,cls_num,xywh,conf,target_iname from tb_o_data "
        cursor.execute(sql_select)
        # print(cursor.fetchone())
        targets = []
        class_set = set()
        for row in iter(lambda: cursor.fetchone(), None):
            targets.append(row)
            class_set.add(row['cls_num'])
        # row = cursor.fetchone()
        # while row:
        #     image.append(row)
        #     row = cursor.fetchone()
        print("targets find成功!")

        print(targets)
        print(len(targets))
        print(f'class_set:\tclass_set = {class_set}')
        # keys = targets.keys()




        print("database operation complete!")

except pymysql.MySQLError as err:
    print("query error")
    print(err)

finally:
    coon.close()
